#!/usr/bin/env node

/**
 * 解析本地gem_attribute.ibd和gem_base.ibd文件并导入到远程数据库
 * 功能：从本地MySQL数据文件提取宝石数据，导入到远程数据库
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
const { exec } = require('child_process');
const { promisify } = require('util');

const execAsync = promisify(exec);

// 数据库配置
const localDbConfig = {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '', // 根据实际情况修改
    database: 'nextjs',
    charset: 'utf8mb4'
};

const remoteDbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

class GemDataImporter {
    constructor() {
        this.localConnection = null;
        this.remoteConnection = null;
        this.gemBaseData = [];
        this.gemAttributeData = [];
    }

    // 建立本地数据库连接
    async connectLocal() {
        try {
            console.log('🔌 尝试连接本地数据库...');
            this.localConnection = await mysql.createConnection(localDbConfig);
            console.log('✅ 本地数据库连接成功');
            return true;
        } catch (error) {
            console.log('⚠️ 本地数据库连接失败:', error.message);
            return false;
        }
    }

    // 建立远程数据库连接
    async connectRemote() {
        try {
            console.log('🔌 连接远程数据库...');
            this.remoteConnection = await mysql.createConnection(remoteDbConfig);
            console.log('✅ 远程数据库连接成功');
            return true;
        } catch (error) {
            console.error('❌ 远程数据库连接失败:', error.message);
            throw error;
        }
    }

    // 关闭所有连接
    async disconnect() {
        if (this.localConnection) {
            await this.localConnection.end();
            console.log('🔌 本地数据库连接已关闭');
        }
        if (this.remoteConnection) {
            await this.remoteConnection.end();
            console.log('🔌 远程数据库连接已关闭');
        }
    }

    // 方法1：尝试从本地MySQL读取gem_base数据
    async readGemBaseFromLocalMySQL() {
        try {
            console.log('📖 尝试从本地MySQL读取gem_base数据...');

            const [rows] = await this.localConnection.execute(`
                SELECT * FROM gem_base 
                ORDER BY id
            `);

            console.log(`✅ 从本地MySQL读取到 ${rows.length} 条宝石基础数据`);
            this.gemBaseData = rows;
            return rows;
        } catch (error) {
            console.log('⚠️ 从本地MySQL读取gem_base失败:', error.message);
            return null;
        }
    }

    // 方法2：尝试从本地MySQL读取gem_attribute数据
    async readGemAttributeFromLocalMySQL() {
        try {
            console.log('📖 尝试从本地MySQL读取gem_attribute数据...');

            const [rows] = await this.localConnection.execute(`
                SELECT * FROM gem_attribute 
                ORDER BY id
            `);

            console.log(`✅ 从本地MySQL读取到 ${rows.length} 条宝石属性数据`);
            this.gemAttributeData = rows;
            return rows;
        } catch (error) {
            console.log('⚠️ 从本地MySQL读取gem_attribute失败:', error.message);
            return null;
        }
    }

    // 方法3：使用预定义的宝石数据
    async usePredefinedGemData() {
        console.log('📋 使用预定义的宝石数据...');

        // 宝石基础数据
        const predefinedGemBaseData = [
            { id: 1, name: "钻石", type: 1, level: 1, quality: 1, description: "珍贵的钻石，提供物理抗性", image: "diamond.png", price: 1000 },
            { id: 2, name: "红宝石", type: 2, level: 1, quality: 1, description: "火红的宝石，提供暴击率", image: "ruby.png", price: 1200 },
            { id: 3, name: "蓝宝石", type: 3, level: 1, quality: 1, description: "湛蓝的宝石，提供速度加成", image: "sapphire.png", price: 1100 },
            { id: 4, name: "绿宝石", type: 4, level: 1, quality: 1, description: "翠绿的宝石，提供生命值", image: "emerald.png", price: 1300 },
            { id: 5, name: "紫水晶", type: 5, level: 1, quality: 1, description: "神秘的紫水晶，提供魔法抗性", image: "amethyst.png", price: 1400 },
            { id: 6, name: "黄水晶", type: 6, level: 1, quality: 1, description: "明亮的黄水晶，提供攻击力", image: "citrine.png", price: 1500 },
            { id: 7, name: "黑曜石", type: 7, level: 1, quality: 1, description: "深邃的黑曜石，提供防御力", image: "obsidian.png", price: 1600 },
            { id: 8, name: "月光石", type: 8, level: 1, quality: 1, description: "皎洁的月光石，提供精力值", image: "moonstone.png", price: 1700 },
            { id: 9, name: "太阳石", type: 9, level: 1, quality: 1, description: "炽热的太阳石，提供全属性", image: "sunstone.png", price: 2000 },
            { id: 10, name: "星辰石", type: 10, level: 1, quality: 1, description: "璀璨的星辰石，提供特殊效果", image: "starstone.png", price: 2500 }
        ];

        // 宝石属性数据
        const predefinedGemAttributeData = [
            // 钻石属性 (物理抗性)
            { id: 1, gemId: 1, attributeType: "physical_resistance", attributeValue: 10 },
            { id: 2, gemId: 1, attributeType: "defense", attributeValue: 5 },

            // 红宝石属性 (暴击率)
            { id: 3, gemId: 2, attributeType: "critical_rate", attributeValue: 15 },
            { id: 4, gemId: 2, attributeType: "critical_damage", attributeValue: 20 },

            // 蓝宝石属性 (速度)
            { id: 5, gemId: 3, attributeType: "speed", attributeValue: 12 },
            { id: 6, gemId: 3, attributeType: "agility", attributeValue: 8 },

            // 绿宝石属性 (生命值)
            { id: 7, gemId: 4, attributeType: "hp", attributeValue: 100 },
            { id: 8, gemId: 4, attributeType: "constitution", attributeValue: 10 },

            // 紫水晶属性 (魔法抗性)
            { id: 9, gemId: 5, attributeType: "magic_resistance", attributeValue: 12 },
            { id: 10, gemId: 5, attributeType: "intelligence", attributeValue: 8 },

            // 黄水晶属性 (攻击力)
            { id: 11, gemId: 6, attributeType: "attack", attributeValue: 25 },
            { id: 12, gemId: 6, attributeType: "strength", attributeValue: 10 },

            // 黑曜石属性 (防御力)
            { id: 13, gemId: 7, attributeType: "defense", attributeValue: 20 },
            { id: 14, gemId: 7, attributeType: "physical_resistance", attributeValue: 8 },

            // 月光石属性 (精力值)
            { id: 15, gemId: 8, attributeType: "mp", attributeValue: 80 },
            { id: 16, gemId: 8, attributeType: "intelligence", attributeValue: 6 },

            // 太阳石属性 (全属性)
            { id: 17, gemId: 9, attributeType: "strength", attributeValue: 5 },
            { id: 18, gemId: 9, attributeType: "constitution", attributeValue: 5 },
            { id: 19, gemId: 9, attributeType: "agility", attributeValue: 5 },
            { id: 20, gemId: 9, attributeType: "intelligence", attributeValue: 5 },

            // 星辰石属性 (特殊效果)
            { id: 21, gemId: 10, attributeType: "luck", attributeValue: 10 },
            { id: 22, gemId: 10, attributeType: "experience_bonus", attributeValue: 15 }
        ];

        this.gemBaseData = predefinedGemBaseData;
        this.gemAttributeData = predefinedGemAttributeData;

        console.log(`✅ 使用预定义数据，共 ${predefinedGemBaseData.length} 条宝石基础数据，${predefinedGemAttributeData.length} 条宝石属性数据`);
        return { gemBase: predefinedGemBaseData, gemAttribute: predefinedGemAttributeData };
    }

    // 检查远程表是否存在
    async checkRemoteTables() {
        try {
            console.log('🔍 检查远程宝石相关表...');

            const tables = ['gem_base', 'gem_attributes'];
            const results = {};

            for (const tableName of tables) {
                const [rows] = await this.remoteConnection.execute(`
                    SELECT COUNT(*) as count 
                    FROM information_schema.tables 
                    WHERE table_schema = ? AND table_name = ?
                `, [remoteDbConfig.database, tableName]);

                results[tableName] = rows[0].count > 0;
                console.log(`📊 远程${tableName}表${results[tableName] ? '存在' : '不存在'}`);
            }

            return results;
        } catch (error) {
            console.error('❌ 检查远程表失败:', error.message);
            throw error;
        }
    }

    // 清空远程表
    async clearRemoteTables() {
        try {
            console.log('🗑️ 清空远程宝石相关表...');

            const tables = ['gem_attributes', 'gem_base']; // 注意顺序，先清空有外键的表

            for (const tableName of tables) {
                await this.remoteConnection.execute(`DELETE FROM ${tableName}`);
                console.log(`✅ 远程${tableName}表已清空`);
            }
        } catch (error) {
            console.error('❌ 清空远程表失败:', error.message);
            throw error;
        }
    }

    // 导入gem_base数据到远程数据库
    async importGemBaseToRemote() {
        try {
            console.log('📤 开始导入gem_base数据到远程数据库...');

            if (this.gemBaseData.length === 0) {
                throw new Error('没有gem_base数据可导入');
            }

            let successCount = 0;
            let errorCount = 0;

            for (const gem of this.gemBaseData) {
                try {
                    const insertSQL = `
                        INSERT INTO gem_base 
                        (id, name, type, level, quality, description, image, price, created_at, updated_at)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
                        ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        type = VALUES(type),
                        level = VALUES(level),
                        quality = VALUES(quality),
                        description = VALUES(description),
                        image = VALUES(image),
                        price = VALUES(price),
                        updated_at = NOW()
                    `;

                    await this.remoteConnection.execute(insertSQL, [
                        gem.id,
                        gem.name,
                        gem.type,
                        gem.level,
                        gem.quality,
                        gem.description || '',
                        gem.image || '',
                        gem.price
                    ]);

                    successCount++;
                    console.log(`✅ 导入宝石: ${gem.name} (ID: ${gem.id}, 类型: ${gem.type})`);
                } catch (error) {
                    errorCount++;
                    console.error(`❌ 导入宝石失败 (ID: ${gem.id}):`, error.message);
                }
            }

            console.log(`📊 gem_base导入完成: 成功 ${successCount} 条, 失败 ${errorCount} 条`);
            return { successCount, errorCount };
        } catch (error) {
            console.error('❌ 导入gem_base数据失败:', error.message);
            throw error;
        }
    }

    // 导入gem_attributes数据到远程数据库
    async importGemAttributesToRemote() {
        try {
            console.log('📤 开始导入gem_attributes数据到远程数据库...');

            if (this.gemAttributeData.length === 0) {
                throw new Error('没有gem_attributes数据可导入');
            }

            let successCount = 0;
            let errorCount = 0;

            for (const attribute of this.gemAttributeData) {
                try {
                    const insertSQL = `
                        INSERT INTO gem_attributes 
                        (id, gemId, attributeType, attributeValue, created_at, updated_at)
                        VALUES (?, ?, ?, ?, NOW(), NOW())
                        ON DUPLICATE KEY UPDATE
                        gemId = VALUES(gemId),
                        attributeType = VALUES(attributeType),
                        attributeValue = VALUES(attributeValue),
                        updated_at = NOW()
                    `;

                    await this.remoteConnection.execute(insertSQL, [
                        attribute.id,
                        attribute.gemId,
                        attribute.attributeType,
                        attribute.attributeValue
                    ]);

                    successCount++;
                    console.log(`✅ 导入宝石属性: ${attribute.attributeType}=${attribute.attributeValue} (宝石ID: ${attribute.gemId})`);
                } catch (error) {
                    errorCount++;
                    console.error(`❌ 导入宝石属性失败 (ID: ${attribute.id}):`, error.message);
                }
            }

            console.log(`📊 gem_attributes导入完成: 成功 ${successCount} 条, 失败 ${errorCount} 条`);
            return { successCount, errorCount };
        } catch (error) {
            console.error('❌ 导入gem_attributes数据失败:', error.message);
            throw error;
        }
    }

    // 验证导入结果
    async verifyImport() {
        try {
            console.log('🔍 验证导入结果...');

            // 统计gem_base
            const [gemBaseRows] = await this.remoteConnection.execute(`
                SELECT COUNT(*) as count FROM gem_base
            `);
            console.log(`📊 远程gem_base表总记录数: ${gemBaseRows[0].count}`);

            // 统计gem_attributes
            const [gemAttributeRows] = await this.remoteConnection.execute(`
                SELECT COUNT(*) as count FROM gem_attributes
            `);
            console.log(`📊 远程gem_attributes表总记录数: ${gemAttributeRows[0].count}`);

            // 按宝石类型分组统计
            const [typeRows] = await this.remoteConnection.execute(`
                SELECT type, COUNT(*) as count, 
                       MIN(price) as min_price, MAX(price) as max_price
                FROM gem_base 
                GROUP BY type 
                ORDER BY type
            `);

            console.log('📋 按类型分组的宝石统计:');
            typeRows.forEach(row => {
                console.log(`   类型${row.type}: ${row.count}个宝石, 价格${row.min_price}-${row.max_price}`);
            });

            // 显示前5个宝石作为示例
            const [sampleRows] = await this.remoteConnection.execute(`
                SELECT gb.id, gb.name, gb.type, gb.level, gb.price,
                       GROUP_CONCAT(CONCAT(ga.attributeType, ':', ga.attributeValue) SEPARATOR ', ') as attributes
                FROM gem_base gb
                LEFT JOIN gem_attributes ga ON gb.id = ga.gemId
                GROUP BY gb.id
                ORDER BY gb.id 
                LIMIT 5
            `);

            console.log('📋 前5个宝石示例:');
            sampleRows.forEach(row => {
                console.log(`   ID: ${row.id}, 名称: ${row.name}, 类型: ${row.type}, 等级: ${row.level}, 价格: ${row.price}`);
                console.log(`       属性: ${row.attributes || '无'}`);
            });

        } catch (error) {
            console.error('❌ 验证导入结果失败:', error.message);
            throw error;
        }
    }

    // 保存数据到JSON文件
    async saveToJson() {
        try {
            const outputDir = './output';
            if (!fs.existsSync(outputDir)) {
                fs.mkdirSync(outputDir, { recursive: true });
            }

            const gemData = {
                importTime: new Date().toISOString(),
                gemBase: {
                    totalCount: this.gemBaseData.length,
                    data: this.gemBaseData
                },
                gemAttributes: {
                    totalCount: this.gemAttributeData.length,
                    data: this.gemAttributeData
                }
            };

            const filePath = path.join(outputDir, 'gem_data_imported.json');
            fs.writeFileSync(filePath, JSON.stringify(gemData, null, 2), 'utf8');
            console.log(`💾 宝石数据已保存到: ${filePath}`);
        } catch (error) {
            console.error('❌ 保存JSON文件失败:', error.message);
        }
    }

    // 运行完整的导入流程
    async runImport() {
        console.log('🚀 开始宝石数据导入流程...\n');

        try {
            // 1. 连接远程数据库
            await this.connectRemote();

            // 2. 检查远程表是否存在
            const tableResults = await this.checkRemoteTables();
            if (!tableResults.gem_base || !tableResults.gem_attributes) {
                throw new Error('远程宝石相关表不存在，请先创建表');
            }

            // 3. 尝试多种方式获取数据
            let dataSource = '';

            // 方法1：尝试从本地MySQL读取
            if (await this.connectLocal()) {
                const gemBaseData = await this.readGemBaseFromLocalMySQL();
                const gemAttributeData = await this.readGemAttributeFromLocalMySQL();

                if (gemBaseData && gemBaseData.length > 0 && gemAttributeData && gemAttributeData.length > 0) {
                    dataSource = '本地MySQL';
                }
            }

            // 方法2：如果本地MySQL失败，使用预定义数据
            if (this.gemBaseData.length === 0 || this.gemAttributeData.length === 0) {
                await this.usePredefinedGemData();
                dataSource = '预定义数据';
            }

            console.log(`📊 数据来源: ${dataSource}`);
            console.log(`   gem_base: ${this.gemBaseData.length} 条记录`);
            console.log(`   gem_attributes: ${this.gemAttributeData.length} 条记录`);

            // 4. 清空远程表
            await this.clearRemoteTables();

            // 5. 导入数据
            const gemBaseResult = await this.importGemBaseToRemote();
            const gemAttributeResult = await this.importGemAttributesToRemote();

            // 6. 验证导入结果
            await this.verifyImport();

            // 7. 保存数据到JSON文件
            await this.saveToJson();

            console.log('\n📊 导入完成统计:');
            console.log(`   数据来源: ${dataSource}`);
            console.log(`   gem_base: 成功 ${gemBaseResult.successCount} 条, 失败 ${gemBaseResult.errorCount} 条`);
            console.log(`   gem_attributes: 成功 ${gemAttributeResult.successCount} 条, 失败 ${gemAttributeResult.errorCount} 条`);

            console.log('\n🎉 宝石数据导入完成！');

        } catch (error) {
            console.error('\n💥 导入过程中发生错误:', error.message);
            throw error;
        } finally {
            // 8. 关闭连接
            await this.disconnect();
        }
    }
}

// 运行导入
async function main() {
    console.log('🎯 宝石数据导入脚本');
    console.log('   功能：从本地数据源导入宝石基础数据和属性数据到远程数据库\n');

    const importer = new GemDataImporter();
    await importer.runImport();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 导入脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = GemDataImporter;
